const mysql = require('mysql2/promise')

async function checkRealStats () {
  try {
    const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'GKL9512357',
      database: 'novel_platform'
    })

    console.log('🔍 检查数据库中的真实统计数据...\n')

    // 总用户数
    const [totalRows] = await connection.execute(
      'SELECT COUNT(*) as total FROM users'
    )
    console.log('📊 总用户数:', totalRows[0].total)

    // 活跃用户数（30天内登录）
    const [activeRows] = await connection.execute(`
      SELECT COUNT(*) as active 
      FROM users 
      WHERE last_login_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    `)
    console.log('🟢 活跃用户数（30天内登录）:', activeRows[0].active)

    // VIP用户数
    const [vipRows] = await connection.execute(`
      SELECT COUNT(*) as vip 
      FROM users 
      WHERE is_vip = 1 AND vip_expire_date > NOW()
    `)
    console.log('⭐ VIP用户数:', vipRows[0].vip)

    // 今日新增用户数
    const [todayRows] = await connection.execute(`
      SELECT COUNT(*) as today 
      FROM users 
      WHERE DATE(created_at) = CURDATE()
    `)
    console.log('📈 今日新增用户:', todayRows[0].today)

    // 昨日新增用户数
    const [yesterdayRows] = await connection.execute(`
      SELECT COUNT(*) as yesterday 
      FROM users 
      WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
    `)
    console.log('📉 昨日新增用户:', yesterdayRows[0].yesterday)

    // 用户角色分布
    const [roleRows] = await connection.execute(`
      SELECT role, COUNT(*) as count 
      FROM users 
      GROUP BY role
    `)
    console.log('\n👥 用户角色分布:')
    roleRows.forEach(row => {
      console.log(`   ${row.role}: ${row.count}`)
    })

    // 用户状态分布
    const [statusRows] = await connection.execute(`
      SELECT status, COUNT(*) as count 
      FROM users 
      GROUP BY status
    `)
    console.log('\n📋 用户状态分布:')
    statusRows.forEach(row => {
      console.log(`   ${row.status}: ${row.count}`)
    })

    await connection.end()
    console.log('\n✅ 数据库统计查询完成')
  } catch (error) {
    console.error('❌ 查询失败:', error.message)
  }
}

checkRealStats()
